package com.ucom;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.sql.Statement;
import java.util.Scanner;
import java.text.ParseException;
import java.text.SimpleDateFormat;


public class DataModifier {
	private Scanner keyboard = new Scanner(System.in);
	private Connection dbConnection;
	private String query;
	private Statement stmt;

	public DataModifier() {
		try {
			Class.forName("org.apache.derby.jdbc.ClientDriver");
			dbConnection = DriverManager.getConnection(
					"jdbc:derby://localhost:1527/UCOM");
			stmt = dbConnection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

		} catch (ClassNotFoundException cnfe) {
			System.out.println("Error in creating Database conncetion");
			System.out.println(cnfe.getMessage());

		} catch (SQLException sqle) {
			System.out.println("Error in creating Database conncetion");
			System.out.println(sqle.getMessage());
		}

	}
	
	public void modify() {
		System.out.println("The available tables for insert or update are listed below:");
		System.out.println("1. COURSE_INFO");
		System.out.println("2. DEPT_INFO");
		System.out.println("3. OFFICE_INFO");
		System.out.println("4. RACE_INFO");
		System.out.println("5. TRANSCRIPT");
		System.out.println("6. UNIX_COMMAND");
		System.out.println("7. USAGE_HISTORY");
		System.out.println("8. USER_INFO");
		
		System.out.println();
		System.out.print("Please select the table you want to modify: ");
		
		while (!keyboard.hasNextInt()) {
			keyboard.nextLine();
			System.out.println("Please input an interger");
		}
		int selection = keyboard.nextInt();
		
		System.out.print("You want to insert new data(1) or update existing data(2)? ");
		while (!keyboard.hasNextInt()) {
			keyboard.nextLine();
			System.out.println("Please input an interger");
		}
		int option = keyboard.nextInt();
		
		switch (selection) {
		case 1:
			modifyCourseInfo(option);
			break;
		case 2:
			modifyDeptInfo(option);
			break;
		case 3:
			modifyOfficeInfo(option);
			break;
		case 4:
			modifyRaceInfo(option);
			break;
		case 5:
			modifyTranscript(option);
			break;
		case 6:
			modifyUnixCommand(option);
			break;
		case 7:
			modifyUsageHistory(option);
			break;
		default:
			System.out.println("Sorry, not a valid selection.");	
		}
	}
	
	private void modifyCourseInfo(int option) {
		if (option == 1) {
			System.out.println("Please input the course ID, it has to be an int:");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int ID = keyboard.nextInt();
			System.out.println("Please input the course name:");
			String name = keyboard.nextLine();
			System.out.println("Please input the department id which offers the course, it has to be an int: ");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int dept = keyboard.nextInt();
			System.out.println("Please input the level of the course, it has to be an int: ");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int level = keyboard.nextInt();
			try {
				query = "insert into course_info values (" + ID +
				", '" + name + "', " + dept + ", " + level + ")";
				stmt.execute(query);
			}
			catch (SQLIntegrityConstraintViolationException e) {
				System.out.println("The value for the department ID violates the foreign key constraint.");
			}
			catch (SQLException e) {
				System.out.println("Error in inserting the data.");
				System.out.println(e.getMessage());
			}
		}
		else if (option == 2) {
			System.out.println("The SQL statement used is:");
			query = "update course_info set course_name='Advanced C++' where course_id=3";
			System.out.println(query);
			System.out.println("Updating....");
			try {
				stmt.execute(query);
				System.out.println("Done.");
			}
			catch (SQLException e) {
				System.out.println("Error in updating the data.");
				System.out.println(e.getMessage());
			}
		}
		else {
			System.out.println("Please input 1 for insert and 2 for update.");
		}
	}
	
	private void modifyDeptInfo(int option) {
		if (option == 1) {
			System.out.println("Please input the department ID, it has to be an int:");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int ID = keyboard.nextInt();
			System.out.println("Please input the department name:");
			String name = keyboard.nextLine();
			
			try {
				query = "insert into dept_info values (" + ID +
				", '" + name + "')";
				stmt.execute(query);
			}
			catch (SQLIntegrityConstraintViolationException e) {
				System.out.println("The value for the department ID violates the foreign key constraint.");
			}
			catch (SQLException e) {
				System.out.println("Error in inserting the data.");
				System.out.println(e.getMessage());
			}
		}
		else if (option == 2) {
			System.out.println("The SQL statement used is:");
			query = "update dept_info set dept_name='Mathematical Computer Science' where dept_id=8";
			System.out.println(query);
			System.out.println("Updating....");
			try {
				stmt.execute(query);
				System.out.println("Done.");
			}
			catch (SQLException e) {
				System.out.println("Error in updating the data.");
				System.out.println(e.getMessage());
			}
		}
		else {
			System.out.println("Please input 1 for insert and 2 for update.");
		}
	}

	private void modifyOfficeInfo(int option) {
		if (option == 1) {
			System.out.println("Please input the office ID, it has to be an int:");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int ID = keyboard.nextInt();
			System.out.println("Please input the office name:");
			String name = keyboard.nextLine();
			System.out.println("Please input 0 or 1 indicating whether this office has printers: ");
			int hasPrinter;
			do {
				while (!keyboard.hasNextInt()) {
					keyboard.nextLine();
					System.out.println("Please input an interger");
				}
				hasPrinter = keyboard.nextInt();
				if (hasPrinter < 0 || hasPrinter > 1)
					System.out.println("This interger has to be either 0 or 1. Please input again.");
			} while (hasPrinter < 0 || hasPrinter > 1);
			try {
				query = "insert into office_info values (" + ID +
				", '" + name + "', " + hasPrinter + ")";
				stmt.execute(query);
			}
			catch (SQLException e) {
				System.out.println("Error in inserting the data.");
				System.out.println(e.getMessage());
			}
		}
		else if (option == 2) {
			System.out.println("The SQL statement used is:");
			query = "update office_info set has_printer = 1 where office_id = 7";
			System.out.println(query);
			System.out.println("Updating....");
			try {
				stmt.execute(query);
				System.out.println("Done.");
			}
			catch (SQLException e) {
				System.out.println("Error in updating the data.");
				System.out.println(e.getMessage());
			}
		}
		else {
			System.out.println("Please input 1 for insert and 2 for update.");
		}
	}
	
	private void modifyRaceInfo(int option) {
		if (option == 1) {
			System.out.println("Please input the race code, it has to be an int:");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int code = keyboard.nextInt();
			System.out.println("Please input the race name:");
			String name = keyboard.nextLine();
			
			try {
				query = "insert into race_info values (" + code + ", '" + name + "')";
				stmt.execute(query);
			}
			
			catch (SQLException e) {
				System.out.println("Error in inserting the data.");
				System.out.println(e.getMessage());
			}
		}
		else if (option == 2) {
			System.out.println("The SQL statement used is:");
			query = "update race_info set race_name='Chinese' where race_code=3";
			System.out.println(query);
			System.out.println("Updating....");
			try {
				stmt.execute(query);
				System.out.println("Done.");
			}
			catch (SQLException e) {
				System.out.println("Error in updating the data.");
				System.out.println(e.getMessage());
			}
		}
		else {
			System.out.println("Please input 1 for insert and 2 for update.");
		}
	}
	
	private void modifyTranscript(int option) {
		if (option == 1) {
			System.out.println("Please input the user ID");
			String userID = keyboard.nextLine();
			System.out.println("Please input the course ID, it has to be an int:");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int courseID = keyboard.nextInt();
			System.out.println("Please input the score, it has to be an int:");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int score = keyboard.nextInt();
			try {
				query = "insert into transcript values ('" + userID +
				"', " + courseID + ", " + score + ")";
				stmt.execute(query);
			}
			catch (SQLIntegrityConstraintViolationException e) {
				System.out.println("The values you input violate the foreign key constraint.");
			}
			catch (SQLException e) {
				System.out.println("Error in inserting the data.");
				System.out.println(e.getMessage());
			}
		}
		else if (option == 2) {
			System.out.println("The SQL statement used is:");
			query = "update transcript set score=90 where user_id='user5' and course_id=5";
			System.out.println(query);
			System.out.println("Updating....");
			try {
				stmt.execute(query);
				System.out.println("Done.");
			}
			catch (SQLException e) {
				System.out.println("Error in updating the data.");
				System.out.println(e.getMessage());
			}
		}
		else {
			System.out.println("Please input 1 for insert and 2 for update.");
		}
	}
	
	private void modifyUnixCommand(int option) {
		if (option == 1) {
			System.out.println("Please input the unix command:");
			String command = keyboard.nextLine();
			System.out.println("Please input the command category:");
			String category = keyboard.nextLine();
			
			try {
				query = "insert into unix_command values ('" + command +
				"', '" + category + "')";
				stmt.execute(query);
			}
			catch (SQLException e) {
				System.out.println("Error in inserting the data.");
				System.out.println(e.getMessage());
			}
		}
		else if (option == 2) {
			System.out.println("Unix command can not be modified");
		}
		else {
			System.out.println("Please input 1 for insert and 2 for update.");
		}
	}
	
	private void modifyUsageHistory(int option) {
		if (option == 1) {
			System.out.println("Please input the user ID:");
			String user = keyboard.nextLine();
			System.out.println("Please input the session ID, it has to be an int:");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int session = keyboard.nextInt();
			System.out.println("Please input the line number, it has to be an int:");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int line = keyboard.nextInt();
			System.out.println("Please input the command sequence, it has to be an int:");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int sequence = keyboard.nextInt();
			System.out.println("Please input the command:");
			String command = keyboard.nextLine();
			try {
				query = "insert into usage_history values ('" + user +
				"', " + session + ", " + line + ", " + sequence + ", '" + command + "')";
				stmt.execute(query);
			}
			catch (SQLIntegrityConstraintViolationException e) {
				System.out.println("The values you input violate the foreign key constraint.");
			}
			catch (SQLException e) {
				System.out.println("Error in inserting the data.");
				System.out.println(e.getMessage());
			}
		}
		else if (option == 2) {
			System.out.println("The SQL statement used is:");
			query = "update usage_history set session_id=1000 where line_no=3";
			System.out.println(query);
			System.out.println("Updating....");
			try {
				stmt.execute(query);
				System.out.println("Done.");
			}
			catch (SQLException e) {
				System.out.println("Error in updating the data.");
				System.out.println(e.getMessage());
			}
		}
		else {
			System.out.println("Please input 1 for insert and 2 for update.");
		}
	}
	
	private void modifyUserInfo(int option) {
		
		if (option == 1) {
			//user id
			System.out.println("Please input the user ID:");
			String userId = keyboard.nextLine();
			System.out.println("Please input the session ID, it has to be an int:");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			//first name
			
			System.out.println("Please input the first name:");
			String firstName = keyboard.nextLine();
		
			System.out.println("Please input the last name:");
			String lastName = keyboard.nextLine();
			
			//sex
			System.out.println("Please input the sex:");
			String sex = keyboard.nextLine();
			
			//office id
			System.out.println("Please input the office ID, it has to be an int:");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int officeId = keyboard.nextInt();
			
			//graduate
			System.out.println("Please input 0 or 1 indicating whether graduate or not: ");
			int isGraduate;
			do {
				while (!keyboard.hasNextInt()) {
					keyboard.nextLine();
					System.out.println("Please input an interger");
				}
				isGraduate = keyboard.nextInt();
				if (isGraduate < 0 || isGraduate > 1)
					System.out.println("This interger has to be either 0 or 1. Please input again.");
			} while (isGraduate < 0 || isGraduate > 1);
			
			//race code
			System.out.println("Please input the race code, it has to be an int:");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int raceCode = keyboard.nextInt();
			
			//password
			System.out.println("Please input the password:");
			String password = keyboard.nextLine();
			
			//years in using unix
			System.out.println("Please input the years in using unix, it has to be an int:");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int unixYears = keyboard.nextInt();
			
			//enroll date
			System.out.println("Enter enroll date");
			System.out.println("Please enter day(dd):");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int day = keyboard.nextInt();
			
			System.out.println("Please enter month(mm):");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int month = keyboard.nextInt();
			
			System.out.println("Please enter year(yyyy):");
			while (!keyboard.hasNextInt()) {
				keyboard.nextLine();
				System.out.println("Please input an interger");
			}
			int year = keyboard.nextInt();
			
			String date = year + "/" + month + "/" + day;
			java.util.Date utilDate = null;
			java.sql.Date sqlDate = null;
			
			try {
				SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
			  utilDate = formatter.parse(date);
			  sqlDate = new java.sql.Date(utilDate.getTime());
			} catch (ParseException e) {
			  System.out.println(e.toString());
			  e.printStackTrace();
			}

	
			
			try {
				query = "insert into user_info values ('" + userId + "','"+firstName + "','" + lastName + "','" + sex 
				+ "'," + officeId + "," + isGraduate + "," + raceCode + ",'" + password + "'" + unixYears + ", " + sqlDate + ")";
				stmt.execute(query);
			}
			catch (SQLIntegrityConstraintViolationException e) {
				System.out.println("The values you input violate the foreign key constraint.");
			}
			catch (SQLException e) {
				System.out.println("Error in inserting the data.");
				System.out.println(e.getMessage());
			}
		}
		else if (option == 2) {
			System.out.println("The SQL statement used is:");
			query = "update usage_history set session_id=1000 where line_no=3";
			System.out.println(query);
			System.out.println("Updating....");
			try {
				stmt.execute(query);
				System.out.println("Done.");
			}
			catch (SQLException e) {
				System.out.println("Error in updating the data.");
				System.out.println(e.getMessage());
			}
		}
		else {
			System.out.println("Please input 1 for insert and 2 for update.");
		}
	}
}
  
